package com.leexuan.ordersystem.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.leexuan.ordersystem.beans.Invoice;

public class InvoiceDB extends DB {

	private String ADD_INVOICE = "INSERT INTO invoice VALUES (?,?,?,?)";
	private String DEL_INVOICE = "DELETE FROM invoice WHERE iid=?";
	private String SUB_INVOICE_REMAIN = "UPDATE invoice SET remain=remain-? WHERE iid=?";

	private String GET_INVOICE_BY_OID = "SELECT i.*,c.name,c.bank,c.account,CONVERT(VARCHAR(30),i.date,111) as idate FROM invoice AS i INNER JOIN [order] AS o On i.oid=o.oid INNER JOIN customer AS c ON o.cid=c.cid WHERE i.oid=?";

	private String GET_INVOICES = "SELECT i.*,c.name,c.bank,c.account,CONVERT(VARCHAR(30),i.date,111) as idate FROM invoice AS i INNER JOIN [order] AS o On i.oid=o.oid INNER JOIN customer AS c ON o.cid=c.cid";

	private String GET_INVOICES_FULL = "SELECT i.*,c.name,c.bank,c.account,CONVERT(VARCHAR(30),i.date,111) as idate FROM invoice AS i INNER JOIN [order] AS o On i.oid=o.oid INNER JOIN customer AS c ON o.cid=c.cid WHERE i.remain=0";

	private String GET_INVOICES_EMPTY = "SELECT i.*,c.name,c.bank,c.account,CONVERT(VARCHAR(30),i.date,111) as idate FROM invoice AS i INNER JOIN [order] AS o On i.oid=o.oid INNER JOIN customer AS c ON o.cid=c.cid WHERE i.remain>0";

	private String HAS_INVOICE = "SELECT count(*) AS number FROM invoice WHERE oid=?";

	public Invoice getInvoice(int oid) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Invoice invoice = new Invoice();
		try {
			pstmt = conn.prepareStatement(GET_INVOICE_BY_OID);
			pstmt.setInt(1, oid);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				invoice.setOid(rs.getInt("oid"));
				invoice.setIid(rs.getInt("iid"));
				invoice.setTotal(rs.getFloat("total"));
				invoice.setRemain(rs.getFloat("remain"));

				invoice.setDate(rs.getString("idate"));
				invoice.setBank(rs.getString("bank"));
				invoice.setName(rs.getString("name"));
				invoice.setAccount(rs.getString("account"));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				conn.close();
				conn = null;
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
		return invoice;
	}

	public boolean hasInvoice(int oid) {
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			pstmt = conn.prepareStatement(HAS_INVOICE);
			pstmt.setInt(1, oid);
			rs = pstmt.executeQuery();

			int number = 0;

			rs.next();

			number = rs.getInt("number");

			if (number == 1) {
				return true;
			} else {
				return false;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				conn.close();
				conn = null;
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}

		return false;

	}

	public boolean addInvoice(Invoice in) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(ADD_INVOICE);

			pstmt.setInt(1, in.getOid());
			pstmt.setFloat(2, in.getTotal());
			pstmt.setString(3, in.getDate());
			pstmt.setFloat(4, in.getRemain());

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public boolean subInvoiceRemain(float sub, int iid) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(SUB_INVOICE_REMAIN);

			pstmt.setFloat(1, sub);
			pstmt.setInt(2, iid);

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public boolean delInvoice(int iid) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(DEL_INVOICE);

			pstmt.setInt(1, iid);

			return pstmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
					pstmt = null;
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
	}

	public ArrayList getInvoices() throws SQLException {

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		ArrayList invoices = new ArrayList();

		try {
			pstmt = conn.prepareStatement(GET_INVOICES, ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			rs = pstmt.executeQuery();

			while (rs.next()) {

				Invoice invoice = new Invoice();

				invoice.setOid(rs.getInt("oid"));
				invoice.setIid(rs.getInt("iid"));
				invoice.setTotal(rs.getFloat("total"));
				invoice.setRemain(rs.getFloat("remain"));

				invoice.setDate(rs.getString("idate"));
				invoice.setBank(rs.getString("bank"));
				invoice.setName(rs.getString("name"));
				invoice.setAccount(rs.getString("account"));

				invoices.add(invoice);

			}

		} catch (SQLException ex) {
			ex.printStackTrace();

			throw new SQLException("SQLException on : DAO");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}

		}
		return invoices;
	}

	public ArrayList getInvoicesFull() throws SQLException {

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		ArrayList invoices = new ArrayList();

		try {
			pstmt = conn.prepareStatement(GET_INVOICES_FULL, ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			rs = pstmt.executeQuery();

			while (rs.next()) {

				Invoice invoice = new Invoice();

				invoice.setOid(rs.getInt("oid"));
				invoice.setIid(rs.getInt("iid"));
				invoice.setTotal(rs.getFloat("total"));
				invoice.setRemain(rs.getFloat("remain"));

				invoice.setDate(rs.getString("idate"));
				invoice.setBank(rs.getString("bank"));
				invoice.setName(rs.getString("name"));
				invoice.setAccount(rs.getString("account"));

				invoices.add(invoice);

			}

		} catch (SQLException ex) {
			ex.printStackTrace();

			throw new SQLException("SQLException on : DAO");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}

		}
		return invoices;
	}

	public ArrayList getInvoicesEmpty() throws SQLException {

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		ArrayList invoices = new ArrayList();

		try {
			pstmt = conn.prepareStatement(GET_INVOICES_EMPTY, ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			rs = pstmt.executeQuery();

			while (rs.next()) {

				Invoice invoice = new Invoice();

				invoice.setOid(rs.getInt("oid"));
				invoice.setIid(rs.getInt("iid"));
				invoice.setTotal(rs.getFloat("total"));
				invoice.setRemain(rs.getFloat("remain"));

				invoice.setDate(rs.getString("idate"));
				invoice.setBank(rs.getString("bank"));
				invoice.setName(rs.getString("name"));
				invoice.setAccount(rs.getString("account"));

				invoices.add(invoice);

			}

		} catch (SQLException ex) {
			ex.printStackTrace();

			throw new SQLException("SQLException on : DAO");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}

		}
		return invoices;
	}
}
